/*
    Change the iWC BAEOrderProductGetQuantityOnHand to match the requirements communicated
    by Edie in the discussion of DT 199.
*/
CREATE procedure [dbo].[BAEOrderProductGetQuantityOnHand] @ProductCode varchar(64) AS
DECLARE @avail decimal(24)
DECLARE @isKit bit
DECLARE @stockType varchar(20)
-- the string constants must match StockType enum in OrderProduct:
-- NotAKit, KitWithStockItems, and KitWithNoStockItems
SELECT TOP 1 @isKit = [IS_KIT] FROM [dbo].[Product] WHERE [PRODUCT_CODE] = @ProductCode
IF @isKit = 1
BEGIN
    SELECT @avail = MIN((inv.[QUANTITY_ON_HAND]-inv.[QUANTITY_RESERVED])/kit.[ITEM_QUANTITY])
        FROM [dbo].[Product_Kit] kit
        INNER JOIN [dbo].[Product] prod ON kit.[ITEM_PRODUCT_CODE] = prod.[PRODUCT_CODE]
        INNER JOIN [dbo].[Product_Inventory] inv ON prod.[PRODUCT_CODE] = inv.[PRODUCT_CODE]
            AND inv.[LOCATION] = prod.[LOCATION]
        WHERE kit.[PRODUCT_CODE] = @ProductCode
            AND prod.[STOCK_ITEM] = 1  
            AND kit.[ITEM_QUANTITY]>0
    IF EXISTS (SELECT TOP 1 prod.[STOCK_ITEM]
        FROM [dbo].[Product_Kit] kit
            INNER JOIN [dbo].[Product] prod ON kit.[ITEM_PRODUCT_CODE] = prod.[PRODUCT_CODE]
            WHERE kit.[PRODUCT_CODE] = @ProductCode
            AND prod.[STOCK_ITEM] = 1 )
        SET @stockType = 'KitWithStockItems'
        ELSE SET @stockType = 'KitWithNoStockItems'

END
ELSE
BEGIN
    SELECT @avail = ISNULL(inv.[QUANTITY_ON_HAND]-inv.[QUANTITY_RESERVED], 0)
        FROM [dbo].[Product] prod
        INNER JOIN [dbo].[Product_Inventory] inv ON prod.[PRODUCT_CODE] = inv.[PRODUCT_CODE]
            AND inv.[LOCATION] = prod.[LOCATION]
        WHERE  prod.[PRODUCT_CODE] = @ProductCode
            AND prod.[STOCK_ITEM] = 1
    SET @stockType = 'NotAKit'
END
SELECT (@avail) AS 'Available', @stockType as 'StockType'

GO